<%@ page language="java" contentType="text/html; charset=UTF-8"%>
<%@include file="/include/common/gridbackhead.jsp"%>
<%!
private class PageAction extends BaseAction{
	ResultSet rs = null;
	//加載頁面默認數據
	protected boolean onLoadDefault() throws Exception{
		if(!form.isNullOrEmpty("ptablesn")){
			form.spsv("rtablesn", form.gpsv("ptablesn"));
			form.spsv("dtablesn", form.gpsv("ptablesn"));
			onSearchRetrieveResult();
		}
		return true ;
	}
	
	protected void EndProcess() throws Exception{
		if("save".equals(form.getAction()) || "copy".equals(form.getAction())){
			onSearchRetrieveResult();
			form.setActionValue(form.getOldActionValue());
		}
	}
	
	// 取出查询結果
	protected void onSearchRetrieveResult() throws Exception{
		if("search".equals(form.getAction())){
			if("s2".equals(form.getActionValue())){
				form.spsv("rtablesn", form.gpsv("dtablesn"));
			}else if("s1".equals(form.getActionValue())){
				form.spsv("rtablesn", form.gpsv("ptablesn"));
			}
		}
		
		ResultSet rs = null;
		//1.取出ajax 組件
		rs = utils.executeQuery("pan",onSearchGetSql());
		ret.SetSearchResult(rs);
	}
	
	//取出查詢語句
	protected String onSearchGetSql() throws Exception{
		StringBuilderEx sql =  new StringBuilderEx("select b.*,a.sn as tablesn1,a.tablename,a.datasource,a.[database],a.rem as tablecnname from pan..pan_table a left join pan..pan_table_detail b on a.sn = b.tablesn ");
		if("s3".equals(form.getActionValue())){
			sql.Append(" where b.name like '%%%1$s%%'",form.gpsv("fieldname"));
		}else{
			sql.Append(" where a.sn = '%1$s'",form.gpsv("rtablesn"));
		}
		sql.Append(" order by srt");
		return sql.toString();
	}
	
	//执行存储
	protected void doSave() throws Exception{
		StringBuilderEx sql = new StringBuilderEx();
		if("sav1".equals(form.getActionValue())){
			CrudDataEntity table = new CrudDataEntity(utils,"pan","pan..pan_table","sn");
			table.getSelf(form.gpsv("rtablesn"));
			
			BaseDataEntities records = form.getrecords("grid1_records");
			
			for (int i=0;i<records.size();i++){
				BaseDataEntity record = (BaseDataEntity)records.get(i);
				CrudDataEntity column = record.getCrudDataEntity("pan","pan..pan_table_detail","sn","tablesn,name,caption,rem,type,defalvalue,srt");
				column.setSpportSimple(true);
				column.setDebug(true);
				column.setUtils(utils);
				column.appendSaveCols();
				
				if(column.isNullOrEmpty("tablesn") 
					||column.isNullOrEmpty("name")
					||column.isNullOrEmpty("type")){
					continue;
				}
				
				if(column.isNullOrEmpty("caption")){
					if("enab".equals(column.gpsv("name"))){
						column.spsv("caption", "是否啟用");
					}else if("prgName".equals(column.gpsv("name"))){
						column.spsv("caption", "修改程序名");
					}else if("crtDay".equals(column.gpsv("name"))){
						column.spsv("caption", "建檔日期");
					}else if("crtName".equals(column.gpsv("name"))){
						column.spsv("caption", "建檔人");
					}else if("crtIp".equals(column.gpsv("name"))){
						column.spsv("caption", "建檔IP");
					}else if("updDay".equals(column.gpsv("name"))){
						column.spsv("caption", "修改日期");
					}else if("updName".equals(column.gpsv("name"))){
						column.spsv("caption", "修改人");
					}else if("updIp".equals(column.gpsv("name"))){
						column.spsv("caption", "修改IP");
					}
				}
				column.save();
				
				registerPanICaption(column.gpsv("name"),column.gpsv("caption"));
				
				if(!isExistColoumn(column,table)){
					sql.Reset();
					sql.Append(" alter table %1$s..%2$s add [%3$s] %4$s not null default(%5$s) \r\n",
						table.gpsv("database"),
						table.gpsv("tablename"),
						column.gpsv("name"),
						column.gpsv("type"),
						column.gpsv("defalvalue","''"));
					out(sql.toString());
					utils.executeUpdate(table.gpsv("datasource"), sql.toString());
				}
			}
			ret.setOkInfo("儲存成功!");
		}else if("sav2".equals(form.getActionValue())){
			CrudDataEntity table = new CrudDataEntity(utils,"pan","pan..pan_table","sn");
			table.getSelf(form.gpsv("rtablesn"));
			sql.Reset();
			//insert into info5.pan.dbo.pan_table_detail(tablesn,name,type)
			sql.Append("SELECT %1$s as tablesn,a.name, ",table.gpsv("sn"));
			sql.Append("case when c.name = 'nvarchar' or c.name = 'nchar'  ");
			sql.Append("then c.name+'('+rtrim(CONVERT(nchar(10),a.length/2))+')' ");
			sql.Append("when c.name = 'decimal' ");
			sql.Append(" then c.name+'('+rtrim(CONVERT(nchar(10),a.xprec))+','+rtrim(CONVERT(nchar(10),a.xscale))+')' ");
			sql.Append(" else c.name end as type");
			
			sql.Append(" FROM %1$s..syscolumns a,%1$s..sysobjects b,%1$s..systypes c WHERE a.id=b.id and b.xtype='u' ",table.gpsv("database"));  
			sql.Append(" and a.xtype=c.xtype  ");
			sql.Append(" and b.name = '%1$s' and a.name <> 'sn' and c.name <> 'sysname' ",table.gpsv("tablename"));
			sql.Append(" order by b.name,a.colorder ");
			
			
			rs = utils.executeQuery(table.gpsv("datasource"), sql.toString());
			BaseDataEntities rows = BaseDataEntities.FromResultSet(rs);
			out(sql.toString());
			int i = rows.size()  ==7 ? 100 : 1;
			for(int j=0;j<rows.size();j++){
				BaseDataEntity row = (BaseDataEntity)rows.get(j);
				
				CrudDataEntity column = new CrudDataEntity(utils,"pan","pan..pan_table_detail","sn");
				column.setSpportSimple(true);
				column.setDebug(true);
				//column.setExecute(false);
				column.spsv("tablesn", row.gpsv("tablesn"));
				column.spsv("name", row.gpsv("name"));
				String sn = "";
				if(column.isExist()){
					sn = column.gpsv("sn");
				}
				
				row.CopyTo(column, false);
				column.spsv("sn", sn);
				column.appendSaveCols();
				
				
				if(column.isNullOrEmpty("caption")){
					if("enab".equals(column.gpsv("name"))){
						column.spsv("caption", "是否啟用");
					}else if("prgName".equals(column.gpsv("name"))){
						column.spsv("caption", "修改程序名");
					}else if("crtDay".equals(column.gpsv("name"))){
						column.spsv("caption", "建檔日期");
					}else if("crtName".equals(column.gpsv("name"))){
						column.spsv("caption", "建檔人");
					}else if("crtIp".equals(column.gpsv("name"))){
						column.spsv("caption", "建檔IP");
					}else if("updDay".equals(column.gpsv("name"))){
						column.spsv("caption", "修改日期");
					}else if("updName".equals(column.gpsv("name"))){
						column.spsv("caption", "修改人");
					}else if("updIp".equals(column.gpsv("name"))){
						column.spsv("caption", "修改IP");
					}
				}
				
				column.spsv("srt", String.valueOf(i));
				column.save();
				i++;
			}
			ret.setOkInfo("從數據庫中同步成功!");
			
		}else if("sav3".equals(form.getActionValue())){
			BaseDataEntities rows =  BaseDataEntities.FromJSONArrayString("["+
					"{name:\"wfinstance\",caption:\"流程实例\",type:\"nvarchar(100)\",defalvalue:\"\",srt:\"111\"}"+
					",{name:\"wfstate\",caption:\"流程狀態\",type:\"nvarchar(100)\",defalvalue:\"'0'\",srt:\"112\"}"+
					",{name:\"jmstatus\",caption:\"数据狀態\",type:\"nvarchar(100)\",defalvalue:\"\",srt:\"113\"}"+
				"]");
			CrudDataEntity table = new CrudDataEntity(utils,"pan","pan..pan_table","sn");
			table.getSelf(form.gpsv("rtablesn"));
			
			for(int i=0;i<rows.size();i++){
				BaseDataEntity row = (BaseDataEntity)rows.get(i);
				if(row.gpsv("name").equals("wfstate")){row.spsv("defalvalue", "'0'");}
				CrudDataEntity column = row.getCrudDataEntity("pan","pan..pan_table_detail","sn","name,caption,type,defalvalue,srt");
				column.setUtils(utils);
				column.spsv("tablesn", table.getKeyValue());
				column.setSpportSimple(true);
				column.setDebug(true);
				column.setUtils(utils);
				column.appendSaveCols();
				
				if(!column.isExist("tablesn,name"))
					column.save();
				
				if(!isExistColoumn(column,table)){
					sql.Reset();
					sql.Append(" alter table %1$s..%2$s add [%3$s] %4$s not null default(%5$s) \r\n",
						table.gpsv("database"),
						table.gpsv("tablename"),
						column.gpsv("name"),
						column.gpsv("type"),
						column.gpsv("defalvalue","''"));
					out(sql.toString());
					utils.executeUpdate(table.gpsv("datasource"), sql.toString());
				}
			}
			ret.setOkInfo("生成流程欄位成功!");
		}
	}
	
	protected void doCopy() throws Exception{
		if(form.isNullOrEmpty("dtablesn"))return;
		StringBuilderEx sql = new StringBuilderEx();
		CrudDataEntity table = new CrudDataEntity(utils,"pan","pan..pan_table","sn");
		table.getSelf(form.gpsv("dtablesn"));
		
		BaseDataEntities records = form.getrecords("grid1_records");
		for (int i=0;i<records.size();i++){
			BaseDataEntity record = (BaseDataEntity)records.get(i);
			CrudDataEntity column = record.getCrudDataEntity("pan","pan..pan_table_detail","sn","name,caption,type,defalvalue");
			column.spsv("srt", "200");
			column.spsv("sn", "");
			column.spsv("tablesn", table.getKeyValue());
			column.setSpportSimple(true);
			column.setDebug(true);
			column.setUtils(utils);
			column.appendSaveCols();
			
			CrudDataEntity tempColumn = column.Clone();
			if(tempColumn.isExist("tablesn,name")){
				continue;
			}
			
			if(column.isNullOrEmpty("tablesn") 
				||column.isNullOrEmpty("name")
				||column.isNullOrEmpty("type")){
				continue;
			}
			
			if(column.isNullOrEmpty("caption")){
				if("enab".equals(column.gpsv("name"))){
					column.spsv("caption", "是否啟用");
				}else if("prgName".equals(column.gpsv("name"))){
					column.spsv("caption", "修改程序名");
				}else if("crtDay".equals(column.gpsv("name"))){
					column.spsv("caption", "建檔日期");
				}else if("crtName".equals(column.gpsv("name"))){
					column.spsv("caption", "建檔人");
				}else if("crtIp".equals(column.gpsv("name"))){
					column.spsv("caption", "建檔IP");
				}else if("updDay".equals(column.gpsv("name"))){
					column.spsv("caption", "修改日期");
				}else if("updName".equals(column.gpsv("name"))){
					column.spsv("caption", "修改人");
				}else if("updIp".equals(column.gpsv("name"))){
					column.spsv("caption", "修改IP");
				}
			}
			column.save();
			
			if(!isExistColoumn(column,table)){
				sql.Reset();
				sql.Append(" alter table %1$s..%2$s add [%3$s] %4$s not null default(%5$s) \r\n",
					table.gpsv("database"),
					table.gpsv("tablename"),
					column.gpsv("name"),
					column.gpsv("type"),
					column.gpsv("defalvalue","''"));
				out(sql.toString());
				utils.executeUpdate(table.gpsv("datasource"), sql.toString());
			}
		}
		form.spsv("rtablesn", form.gpsv("dtablesn"));
		ret.setOkInfo("複製成功!");
	}
	
	protected boolean onDelete() throws Exception{
		super.onDelete();
		onSearch();
		return true ;
	}
	
	protected void doDelete() throws Exception{
		String sns[] = form.gpsvs("key");
		if(sns.length>0){
			utils.executeUpdate("pan","delete from pan..pan_table_detail where sn in("+StringHelper.arrayToString(sns,"'",",")+")");
		}
	}
	
	public boolean isExistColoumn(BaseDataEntity column,CrudDataEntity table) throws Exception{
		StringBuilderEx sql = new StringBuilderEx();
		sql.Append("SELECT %1$s as tablesn,a.name, ",table.gpsv("sn"));
		sql.Append("case when c.name = 'nvarchar' or c.name = 'nchar'  ");
		sql.Append("then c.name+'('+rtrim(CONVERT(nchar(10),a.length))+')' ");
		sql.Append("when c.name = 'decimal' ");
		sql.Append(" then c.name+'('+rtrim(CONVERT(nchar(10),a.xprec))+','+rtrim(CONVERT(nchar(10),a.xscale))+')' ");
		sql.Append(" else c.name end as type");
		
		sql.Append(" FROM %1$s..syscolumns a,%1$s..sysobjects b,%1$s..systypes c WHERE a.id=b.id and b.xtype='u' ",table.gpsv("database"));  
		sql.Append(" and a.xtype=c.xtype  ");
		sql.Append(" and b.name = '%1$s' and a.name <> 'sn' and c.name <> 'sysname' ",table.gpsv("tablename"));
		sql.Append(" and a.name = '%1$s'", column.gpsv("name"));
		sql.Append(" order by b.name,a.colorder ");
		rs = utils.executeQuery(table.gpsv("datasource"), sql.toString());
		return rs.next();
	}
	
	public void registerPanICaption(String name,String caption) throws Exception{
		if(StringHelper.IsNullOrEmpty(name) 
				|| StringHelper.IsNullOrEmpty(caption)
				|| !utils.isChinese(caption)){
			return ;
		}
		rs = utils.executeQuery("pan", "select * from pan..PAN_ICAPTION  where name= N'"+name+"'");
		StringBuilderEx sql = new StringBuilderEx();
		if(rs.next()){
			sql.Append("update pan..PAN_ICAPTION  set caption = N'%1$s' where sn = '%2$s'" ,caption ,utils.getRsString(rs, "sn"));
		}else{
			sql.Append("insert into pan..pan_icaption (name,caption,crtday,crtname) values('%1$s',N'%2$s',getdate(),'%3$s')",
					name,
					caption,
					utils.getStaffIf());
		}
		utils.executeUpdate("pan", sql.toString());
	}
	
	protected boolean onCustomAction(String action) throws Exception{
		if("getCaption".equalsIgnoreCase(action)){
			return getCaption();
		}
		return super.onCustomAction(action);
	}
	
	//根據名稱取得標題
	protected boolean getCaption() throws Exception{
		PageResult pageResult = ret.getCallResult();
		String name = form.gpsv("name");
		
		if(StringHelper.IsNullOrEmpty(name)){
			pageResult.setRetCode(Errors.NOK);
		}else{
			ResultSet rs = utils.executeQuery("pan", StringHelper.Format("select name,caption from pan..PAN_ICAPTION  where name= N'%1$s'",name));
			if(rs.next()) {
				pageResult.getData().FromResultSet(rs, false);
			}else{
				pageResult.setRetCode(Errors.NOK);
			}
		}
		this.getPage().Output(pageResult.ToJSONString());
		return true;
	}
}
%>
<%@include file="/include/common/gridbackend.jsp"%>